﻿#region DaContractTask
/*----------------------------------------------------------------
// 文件名：DaContractTask.cs
// 功能描述：表dbo.Contract_Task的数据访问层
//
// 
// 创建时间：2009-11-22 DtataAccess template . Ver 5.0.20090413
//
// 修改标识： 
// 修改描述： 
//----------------------------------------------------------------*/
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Globalization;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using EQT.Model;


namespace EQT.Dal
{
    ///可以继承更上层接口,IContractTask ,数据访问层基类DaBase，
    ///DaBase中包含了DbException异常处理静态方法
    public partial class DaContractTask
    {
        #region 自定义 SQL const
        internal const string test = " CT_ID ,REPORT_ID ,CT_TYPE ,CT_LEADER_ID ,CT_ASSIGN_DATE ,CT_TESTER_ID ,CT_STATUS ,CT_BEGIN_DATE ,CT_END_DATE ,CLASS1   ";
        internal const string SQL_ASSIGN_TASK = "UPDATE CONTRACT_TASK SET CT_STATUS={3},CT_LEADER_ID='{1}',CT_TESTER_ID='{2}',CT_ASSIGN_DATE=CONVERT(DATETIME,'{4}') WHERE CT_ID='{0}'";
        internal const string SQL_START_TASK = "UPDATE CONTRACT_TASK SET CT_STATUS={2},CT_BEGIN_DATE=CONVERT(DATETIME,'{1}') WHERE CT_ID='{0}'";
        internal const string SQL_COMPLETE_TASK = "UPDATE CONTRACT_TASK SET CT_STATUS={2},CT_END_DATE=CONVERT(DATETIME,'{1}') WHERE CT_ID='{0}'";
        internal const string SQL_UPDATE_TASK_FILE = "UPDATE PRODUCT_CHECK_CONTRACT SET {1}='{2}' WHERE REPORT_ID='{0}'";
        internal const string SQL_CHECK_PART_FILE = "SELECT COUNT(*) FROM PRODUCT_CHECK_CONTRACT  WHERE REPORT_ID='{0}' AND ({1} IS NOT NULL OR {1} <>'')";
        internal const string SQL_LEADER_CONFIRM = "UPDATE CONTRACT_TASK SET CT_STATUS={1} WHERE CT_ID='{0}'";
        internal const string SQL_UPDATE_CONTRACT_STATUS = "UPDATE PRODUCT_CHECK_CONTRACT SET REPORT_STATUS={1} WHERE REPORT_ID='{0}' ";
        internal const string SQL_CHECK_FILE_ALL_UP = "SELECT (SELECT COUNT(*) AS Fcount FROM Contract_Task " +
                                                      " WHERE (Report_ID = '{0}')) - (SELECT (CASE WHEN fid_ddl_xls IS NULL  " +
                                                      " THEN 0 WHEN fid_ddl_xls = '' THEN 0 ELSE 1 END) + (CASE WHEN fid_drl_xls IS NULL  " +
                                                      " THEN 0 WHEN fid_drl_xls = '' THEN 0 ELSE 1 END) " +
                                                      " FROM Product_Check_Contract WHERE report_id = '{0}')";

        internal const string SQL_RETURN_TASK = "UPDATE CONTRACT_TASK SET CT_STATUS={1}  WHERE CT_ID='{0}'";
        internal const string SQL_CHECK_ITEM_FILE_ISCOMPLETED = "sql";
        #endregion

        /// <summary>
        /// 按条件查询任务记录。（分页）
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public DataSet GetTaskList(string condition, int PageSize, int PageIndex, out int TotalCount)
        {
            DataSet ds = new DataSet();

            string strGetTaskRecords = "select * from V_Task_View ";

            if (!string.IsNullOrEmpty(condition))
            {
                strGetTaskRecords = strGetTaskRecords + condition;
            }

            try
            {
                ds = DaCommon.GetPaginationData(strGetTaskRecords, PageSize, PageIndex, " CT_ID desc", out TotalCount);
                return ds;
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            TotalCount = 0;
            return new DataSet();
        }

        /// <summary>
        /// 查询某条具体的任务信息。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <returns></returns>
        public DataSet GetOneTask(string strCtid)
        {
            DataSet ds = new DataSet();

            string strGetTaskRecords = "select * from V_Task_View where CT_ID='" + strCtid + "'";

            try
            {
                ds = db.ExecuteDataSet(CommandType.Text, strGetTaskRecords);
                return ds;
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }

        /// <summary>
        /// 任务下发，指定主管实验员。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <param name="strLeader">下发人编号</param>
        /// <param name="strTester">实验员编号</param>
        /// <returns></returns>
        public bool TaskAssign(string strCtid, string strLeader, string strTester)
        {
            string strSql = string.Format(SQL_ASSIGN_TASK, strCtid, strLeader, strTester, ((int)CoTestItemStatus.Assigned).ToString(), DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));

            try
            {
                db.ExecuteNonQuery(CommandType.Text, strSql);
                return true;
            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }
        }

        /// <summary>
        /// 任务开始，更新任务状态。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <returns></returns>
        public bool TaskStart(string strCtid)
        {
            string strSql = string.Format(SQL_START_TASK, strCtid, DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), ((int)CoTestItemStatus.Testing).ToString());
            string strContractSql = "Update Product_Check_Contract set Report_Status=" + ((int)Model.ContractStatus.Testing).ToString() + " Where Report_ID=(select report_id from Contract_Task where ct_id='" + strCtid +"')";

            using (DbConnection conn = db.CreateConnection())
            {
                try
                {
                    conn.Open();
                    using (DbTransaction trans = conn.BeginTransaction())
                    {
                        db.ExecuteNonQuery(trans,CommandType.Text, strSql);
                        db.ExecuteNonQuery(trans,CommandType.Text, strContractSql);
                        trans.Commit();
                    }
                    conn.Close();
                    return true;
                }
                catch (Exception ex)
                {
                    HandleDbException(ex);
                    return false;
                }
            }           
        }

        /// <summary>
        /// 任务退回，更新任务状态。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <returns></returns>
        public bool TaskReturn(string strCtid)
        {
            string strSql = string.Format(SQL_RETURN_TASK, strCtid, ((int)CoTestItemStatus.Untread).ToString());

            try
            {
                db.ExecuteNonQuery(CommandType.Text, strSql);
                return true;
            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }
        }

        /// <summary>
        /// 完成任务，更新状态和检测记录文件。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <param name="strFileid"></param>
        /// <returns></returns>
        public bool TaskComplete(string strCtid)
        {
            string strSql = string.Format(SQL_COMPLETE_TASK, strCtid, DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), ((int)CoTestItemStatus.TestDone).ToString());

            try
            {
                if (CheckResultFile(strCtid))
                {
                    db.ExecuteNonQuery(CommandType.Text, strSql);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }
        }

        /// <summary>
        /// 检查某项任务的检测结果文件是否上传。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <returns></returns>
        private bool CheckResultFile(string strCtid)
        {
            DaProductCheckContract dalPc = new DaProductCheckContract(db);
            DaContractTask dalTask = new DaContractTask(db);

            MoContractTask motask = dalTask.GetEntities(" where ct_id='" + strCtid + "'")[0];

            MoProductCheckContract modP = dalPc.GetEntities(" where report_id='" + motask.ReportId + "'")[0];

            if (motask.CtType == "1")
            {
                return modP.FidDdlXls == "" ? false : true;
            }
            else
            {
                return modP.FidDrlXls == "" ? false : true;
            }
        }

        /// <summary>
        /// 组长确认，更新状态。
        /// </summary>
        /// <param name="strCtid">任务编号</param>
        /// <returns></returns>
        public bool TaskLeaderConfirm(string strCtid, string strReportid)
        {
            if (this.CheckItemFileIsCompleted(strCtid, strReportid))
            {
                string strSql = string.Format(SQL_LEADER_CONFIRM, strCtid, ((int)CoTestItemStatus.TaskDone).ToString());

                try
                {
                    db.ExecuteNonQuery(CommandType.Text, strSql);

                    if (CheckAnotherStatus(strCtid, strReportid))
                    {
                        //如果两个项目都已经做完，则更新合状态。
                        string sqlupdatereport = string.Format(SQL_UPDATE_CONTRACT_STATUS, strReportid, ((int)ContractStatus.WaitReport).ToString());
                        db.ExecuteNonQuery(CommandType.Text, sqlupdatereport);

                        return true;
                    }
                    else
                    {
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    HandleDbException(ex);
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 检查某任务文件是否上传。
        /// </summary>
        /// <param name="ctid">任务编号</param>
        /// <param name="reportid">合同编号</param>
        /// <returns></returns>
        private bool CheckItemFileIsCompleted(string ctid, string reportid)
        {
            string strSql = "select {0} from Product_Check_Contract where report_id='{1}'";
            string strSqlTask = "SELECT ct_type from Contract_Task where ct_id='" + ctid + "'";
            string strtype = string.Empty;

            try
            {
                object objtype = db.ExecuteScalar(CommandType.Text, strSqlTask);
                if (objtype == null)
                {
                    return false;
                }
                else
                {
                    strtype = objtype.ToString();
                }
                string strTemp;

                if (strtype == "1")
                {
                    strTemp = string.Format(strSql, "FID_DDL_XLS", reportid);
                }
                else
                {
                    strTemp = string.Format(strSql, "FID_DRL_XLS", reportid);
                }
                object objfile = db.ExecuteScalar(CommandType.Text, strTemp);
                if (objtype == null)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }
        }

        /// <summary>
        /// 检查合同检验项对应的检测记录文件是否全部上传。
        /// </summary>
        /// <param name="reportid">合同编号</param>
        /// <returns></returns>
        private bool CheckFileIsCompleted(string reportid)
        {
            string strSql = string.Format(SQL_CHECK_FILE_ALL_UP, reportid);

            using (DbConnection conn = db.CreateConnection())
            {
                try
                {
                    conn.Open();
                    using (DbTransaction trans = conn.BeginTransaction())
                    {
                        object obj = db.ExecuteScalar(trans, CommandType.Text, strSql);

                        if (int.Parse(obj.ToString()) != 0)
                        {
                            conn.Close();
                            return false;//如果检测项目数-已上传文件数!=0，则说明文件未全部上传。
                        }

                        //string sqlupdatereport = string.Format(SQL_UPDATE_CONTRACT_STATUS, reportid, (int)ContractStatus.WaitReport);
                        //db.ExecuteNonQuery(trans, CommandType.Text, sqlupdatereport);

                        trans.Commit();
                    }
                    conn.Close();
                    return true;
                }
                catch (DbException de)
                {
                    throw de;
                }

            }
        }

        /// <summary>
        /// 检验是否另一个任务已经完成。
        /// </summary>
        /// <returns></returns>
        private bool CheckAnotherStatus(string ctid, string reportid)
        {
            string strSQL = string.Format("SELECT * FROM Contract_Task where report_id='{0}' and ct_id<>'{1}' and ct_status='{2}'", reportid, ctid, ((int)CoTestItemStatus.TaskDone).ToString());
            try
            {
                IDataReader dr = db.ExecuteReader(CommandType.Text, strSQL);
                if (dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }
        }

        /// <summary>
        /// 检查大电流或大电容的检测记录是否上传。
        /// </summary>
        /// <param name="reportid"></param>
        /// <param name="type">1大电流2大电容</param>
        /// <returns>false未上传true已上传</returns>
        public bool CheckResultFileIsCompleted(string reportid, int type)
        {

            string strSql = string.Format(SQL_CHECK_PART_FILE, reportid, (type == 1) ? "FID_DDL_Xls" : "FID_DRL_Xls");

            try
            {
                object obj = db.ExecuteScalar(CommandType.Text, strSql);
                if (int.Parse(obj.ToString()) == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }

        }

        /// <summary>
        /// 根据任务类型，删除检测结果记录文件。
        /// </summary>
        /// <param name="strReportid"></param>
        /// <param name="strType"></param>
        /// <returns></returns>
        public bool RemoveAttath(string strReportid, string strType)
        {
            try
            {

                DaTfiles dalFiles = new DaTfiles(db);
                DaProductCheckContract dalPc = new DaProductCheckContract(db);

                dalPc.DelProductCheckRecordFile(strReportid, int.Parse(strType), dalFiles);
                return true;

            }
            catch (Exception ex)
            {
                HandleDbException(ex);
                return false;
            }


            //using (DbConnection conn = db.CreateConnection())
            //{
            //    try
            //    {
            //        conn.Open();
            //        using (DbTransaction trans = conn.BeginTransaction())
            //        {
            //            string fileid = string.Empty;
            //            MoProductCheckContract modpc = dalPc.GetEntities(" where report_id='" + strReportid + "'")[0];
            //            fileid = strType == "1" ? modpc.FidDdlXls : modpc.FidDrlXls;
            //            string sqlupdate = "update Product_Check_Contract set " + (strType == "1" ? "FID_DDL_Xls" : "FID_DRL_Xls") + " ='' where report_id='" + strReportid + "'";
            //            db.ExecuteNonQuery(trans, CommandType.Text, sqlupdate);
            //            string sqlDeleteRef = "delete from Ref_File where file_id='" + fileid + "'";
            //            rc += db.ExecuteNonQuery(trans, CommandType.Text, sqlDeleteRef);
            //            rc += dalFile.DeleteByWhereClause(String.Format(" WHERE FILE_ID='{0}'", fileid), trans);
            //            trans.Commit();
            //        }
            //        conn.Close();
            //        return true;
            //    }
            //    catch (DbException de)
            //    {
            //        throw de;
            //    }
            //}
        }

        /// <summary>
        /// 为合同创建任务 szj
        /// </summary>
        /// <param name="pcc"></param>
        /// <returns></returns>
        public int CreateContractTaskForPcc(MoProductCheckContract moPcc, MoProductCheckAbility moPca, DbTransaction trans)
        {
            int rc = 0;
            if (!String.IsNullOrEmpty(moPca.TltXlsDdl))//若大电流模板非空
            {
                //添加大电流任务
                MoContractTask task1 = new MoContractTask();
                task1.CtId = SequenceKeys.GenerateCoItemID();
                task1.ReportId = moPcc.ReportId;
                task1.CtType = "1";//大电流是1，大容量是2
                task1.CtStatus = (int)CoTestItemStatus.None;//任务刚刚创建，等待下发
                task1.Class1 = String.Format(EqtAsmKeys.Task_Summary_Format, EqtAsmKeys.Task_DDL_Name, moPca.Pname,
                    moPca.Pmodel, moPca.Ptype, moPcc.CorpName, moPcc.CreateTime.ToShortDateString());
                rc += Add(task1, trans);
                if (log.IsDebugEnabled) log.DebugFormat("添加大电流任务:{0}", task1);
            }
            if (!String.IsNullOrEmpty(moPca.TltXlsDrl))//若大容量模板非空
            {
                //添加大容量任务
                MoContractTask task2 = new MoContractTask();
                task2.CtId = SequenceKeys.GenerateCoItemID();
                task2.ReportId = moPcc.ReportId;
                task2.CtType = "2";//大电流是1，大容量是2
                task2.CtStatus = (int)CoTestItemStatus.None;//任务刚刚创建，等待下发
                task2.Class1 = String.Format(EqtAsmKeys.Task_Summary_Format, EqtAsmKeys.Task_DRL_Name, moPca.Pname,
                    moPca.Pmodel, moPca.Ptype, moPcc.CorpName, moPcc.CreateTime.ToShortDateString());
                rc += Add(task2, trans);
                if (log.IsDebugEnabled) log.DebugFormat("添加大容量任务:{0}", task2);
            }
            return rc;
        }



        /// <summary>
        /// 更新一组任务的状态
        /// </summary>
        const string SQL_UPDATE_GROUP_TASK_STATE = "UPDATE CONTRACT_TASK SET CT_STATUS=@CT_STATUS WHERE CT_TYPE=@CT_TYPE AND  REPORT_ID=@REPORT_ID";

        /// <summary>
        /// 更新一组任务的状态 szj
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="ctType">任务类型：如大电流"1"，大容量"2"</param>
        /// <param name="state"></param>
        /// <returns></returns>
        public int UpdateGroupTaskState(string reportId, string ctType, int state)
        {           
            DbCommand cmd = db.GetSqlStringCommand(SQL_UPDATE_GROUP_TASK_STATE);
            db.AddInParameter(cmd, "CT_STATUS", DbType.Int32, state);
            db.AddInParameter(cmd, "CT_TYPE", DbType.String, ctType);
            db.AddInParameter(cmd, "REPORT_ID", DbType.String, reportId);
            return db.ExecuteNonQuery(cmd);
        }


    }
}
#endregion


